﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using MaterialSkin;
using MaterialSkin.Controls;

using MySql.Data.MySqlClient;

namespace home_material_tool
{
    public partial class MainForm : MaterialForm
    {
        //private MaterialSnackBar snackBar = default;
        private string dbSearchTableName = string.Empty;

        //
        private ulong curPageIndex = 0;
        private ulong recTotalCount = (ulong)CommonSql.RecCountPerPage;
        private ulong totalPageCount = 1;
        private int curPageRecCount = CommonSql.RecCountPerPage;

        //
        private int colorSchemeIndex = 0;

        public MainForm()
        {

            if (Properties.Settings.Default.UpdateRequired)
            {
                //read the last settings
                Properties.Settings.Default.Upgrade();
                Properties.Settings.Default.UpdateRequired = false;
                Properties.Settings.Default.Save();
            }
            //read database connection string
            if (!string.IsNullOrEmpty(Properties.Settings.Default.DBParameter))
            {
                string[] str = Properties.Settings.Default.DBParameter.Split(new string[] { "||||" }, 4, StringSplitOptions.None);
                CommonSql.ConnectionString = string.Format("server={0};port={1};uid={2};pwd={3};database={4};charset=utf8mb4;",
                    str[0], str[1], str[2], str[3], CommonSql.DB);
            }

            colorSchemeIndex = Properties.Settings.Default.ColorSchemeIndex;


            InitializeComponent();

            Common.MSkinManager.LangType = MaterialSkinManager.LanguageType.zh_CN;
            // Set this to false to disable backcolor enforcing on non-materialSkin components
            // This HAS to be set before the AddFormToManage()
            Common.MSkinManager.EnforceBackcolorOnAllComponents = true;

            //Theme and ColorScheme initialized first,
            //thenTheme and ColorScheme changing function should be placed in the global position, and using it to change all 
            //material form each time.
            Common.MSkinManager.Theme = Properties.Settings.Default.Theme == 0 ? MaterialSkinManager.Themes.LIGHT : MaterialSkinManager.Themes.DARK;
            setColor();
            //Common.MSkinManager.ColorScheme = new ColorScheme(Primary.Indigo500, Primary.Indigo700, Primary.Indigo100, Accent.Pink200, TextShade.WHITE);

            //MaterialSkinManager properties
            //only update the added material form color when new form added
            //this will update all the controls in the new form, including the no material controls
            Common.MSkinManager.AddFormToManage(this);

            //reset the color on action panel
            SetActionPanelTheme();
            SetDataGridViewStyle();

        }


        private void SetActionPanelTheme()
        {
            this.actionBarPanel.BackColor = Common.MSkinManager.ColorScheme.PrimaryColor;
        }

        private void SetDataGridViewStyle()
        {
            this.dtGridView.ColumnHeadersDefaultCellStyle.Font = new Font("SimSun", 9);
            this.dtGridView.DefaultCellStyle.Font = new Font("SimSun", 9);
            this.dtGridView.DefaultCellStyle.ForeColor = SystemColors.ControlText;
            this.dtGridView.DefaultCellStyle.BackColor = SystemColors.Window;
            //
            this.dtGridView.AutoGenerateColumns = true;
            //enter or one click can change data
            this.dtGridView.EditMode = DataGridViewEditMode.EditOnEnter;
            //if this, there is no blank row in the end of DataGridView control
            this.dtGridView.AllowUserToAddRows = false;

        }


        private void btnSetting_Click(object sender, EventArgs e)
        {
            SettingForm settingForm = new SettingForm();
            settingForm.ShowDialog(this);
        }


        private void mBtnSearch_Click(object sender, EventArgs e)
        {
            curPageIndex = 1;
            selectDataAndFill();
        }


        private void dtGridView_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {

            MaterialSnackBar snackBar = new MaterialSnackBar();
            if (string.IsNullOrEmpty(dbSearchTableName))
            {
                snackBar.BarType = MaterialSnackBar.SnackBarType.Warning;
                snackBar.Text = "数据表名未设置";
                snackBar.Show(this);
                return;
            }

            try
            {
                string strcolumn = dtGridView.Columns[e.ColumnIndex].Name;
                string strrow = dtGridView.Rows[e.RowIndex].Cells[0].Value.ToString();
                string cellValue = string.Empty;
                if (strcolumn.CompareTo("movie_type") == 0)
                    cellValue = Regex.Replace(dtGridView.CurrentCell.Value.ToString(), @"\s", "");
                else if(strcolumn.CompareTo("country_type") == 0)
                    cellValue = Regex.Replace(dtGridView.CurrentCell.Value.ToString(), @"\s", "");
                else if(strcolumn.CompareTo("released_year") == 0 )
                {
                    cellValue = dtGridView.CurrentCell.Value.ToString().Trim();

                    if (!Regex.IsMatch(cellValue, "^\\d+$"))
                    {
                        return;
                    }

                }
                else if(strcolumn.CompareTo("douban_score") == 0)
                {
                    cellValue = dtGridView.CurrentCell.Value.ToString().Trim();
                    if (!Regex.IsMatch(cellValue, "^[0-9]+(.[0-9]{1,3})?$"))
                    {
                        return;
                    }

                }
                else
                {
                    cellValue = dtGridView.CurrentCell.Value.ToString().Trim();
                }
                //
                if (strcolumn.Equals("hm_status"))
                {
                    switch (cellValue)
                    {
                        case "未下载":
                            cellValue = "0";
                            break;
                        case "已下载":
                            cellValue = "1";
                            break;
                        case "已看":
                            cellValue = "2";
                            break;
                        case "要删":
                            cellValue = "3";
                            break;
                        case "已删":
                            cellValue = "4";
                            break;
                        default:
                            cellValue = "0";
                            break;
                    }

                }

                //string tableName = mRadioMovie.Checked ? CommonSql.MovieTableName : CommonSql.TVSeriesTableName;
                string updateSql = "update " + dbSearchTableName + " set " + strcolumn + "='" + cellValue + "',update_time=SYSDATE() where id=" + strrow;

                MySqlCommand comm = new MySqlCommand(updateSql, CommonSql.MysqlConn);
                int recCnt = comm.ExecuteNonQuery();
                if (recCnt > 0)
                {
                    snackBar.BarType = MaterialSnackBar.SnackBarType.Suceesss;
                    snackBar.Text = "id=" + strrow + "记录" + "更新成功";
                }
                else
                {
                    snackBar.BarType = MaterialSnackBar.SnackBarType.Warning;
                    snackBar.Text = "id=" + strrow + "记录数据没变";
                }

            }
            catch(Exception ex)
            {
                snackBar.BarType = MaterialSnackBar.SnackBarType.Danger;
                snackBar.Text = ex.Message;
            }
            //
            //if(snackBar.BarType != MaterialSnackBar.SnackBarType.Suceesss)
                snackBar.Show(this);
        }

        private void mBtnAdd_Click(object sender, EventArgs e)
        {
            AddEditForm addForm = new AddEditForm();
            addForm.ShowDialog(this);
        }

        private void mBtnDelete_Click(object sender, EventArgs e)
        {
            MaterialSnackBar snackBar = new MaterialSnackBar();
            snackBar.BarType = MaterialSnackBar.SnackBarType.Warning;
            if (string.IsNullOrEmpty(dbSearchTableName))
            {
                snackBar.Text = "数据表名未设置";
                snackBar.Show(this);
                return;
            }
            if(this.dtGridView.SelectedRows.Count == 0)
            {
                snackBar.Text = "未选择记录";
                snackBar.Show(this);
                return;
            }
            try
            {
                string ids = string.Empty;
                for (int i = 0; i < this.dtGridView.SelectedRows.Count; i++)
                {
                    ids += this.dtGridView.SelectedRows[i].Cells[0].Value.ToString() + ",";
                }
                ids = ids.Substring(0, ids.Length - 1);

                string deleteSql = "delete from " + dbSearchTableName + " where id in (" + ids + ");" + "alter table " + dbSearchTableName + " AUTO_INCREMENT=1";
                MySqlCommand comm = new MySqlCommand(deleteSql, CommonSql.MysqlConn);
                int recCnt = comm.ExecuteNonQuery();
                if (recCnt == this.dtGridView.SelectedRows.Count)
                {
                    snackBar.BarType = MaterialSnackBar.SnackBarType.Suceesss;
                    snackBar.Text = "删除成功";
                }
                else
                {
                    snackBar.BarType = MaterialSnackBar.SnackBarType.Warning;
                    snackBar.Text = "未完全删除";
                }
            }
            catch(Exception ex)
            {
                snackBar.BarType = MaterialSnackBar.SnackBarType.Danger;
                snackBar.Text = ex.Message;
            }
            //if(snackBar.BarType != MaterialSnackBar.SnackBarType.Suceesss)
                snackBar.Show(this);

        }



        private void mBtnFirstPage_Click(object sender, EventArgs e)
        {
            curPageIndex = 1;
            selectDataAndFill();
        }

        private void mBtnPrePage_Click(object sender, EventArgs e)
        {
            if (curPageIndex <= 1)
                return;
            curPageIndex--;
            selectDataAndFill();
        }

        private void mBtnNextPage_Click(object sender, EventArgs e)
        {
            if (curPageIndex >= totalPageCount)
                return;
            curPageIndex++;
            selectDataAndFill();
        }

        private void mBtnLastPage_Click(object sender, EventArgs e)
        {
            if (curPageIndex <= 0) return;
            curPageIndex = totalPageCount;
            selectDataAndFill();
        }

        private void mBtnRedictTo_Click(object sender, EventArgs e)
        {
            ulong pageInt = Convert.ToUInt64(mTxtCurPageIndex.Text);
            if (pageInt == curPageIndex || pageInt < 1  || pageInt > totalPageCount)
                return;

            curPageIndex = pageInt;
            selectDataAndFill();

        }

        private async void selectDataAndFill()
        {
            MaterialSnackBar snackBar = new MaterialSnackBar();

            dbSearchTableName = mRadioMovie.Checked ? CommonSql.MovieTableName : CommonSql.TVSeriesTableName;

            string whereStr = " where 1 and ";
            //1.movie name or other's name
            if (!string.IsNullOrWhiteSpace(this.mTxtName.Text))
            {
                if (mRadioEnName.Checked)
                {
                    //名称当中可能有转义字符单引号
                    whereStr += $" locate('{this.mTxtName.Text.Trim().ToLower().Replace("'", "''")}', lower(en_name))";
                }
                else if (mRadioCnName.Checked)
                {
                    whereStr += $" locate('{this.mTxtName.Text.Trim().Replace("'", "''")}', cn_name)";
                }
                whereStr += " and ";
            }
            //2.hm_status
            if (mChkboxNotDownload.Checked || mChkboxDownloaded.Checked || mChkboxSeen.Checked ||
                mChkboxToDelete.Checked || mChkboxDeleted.Checked)
            {
                whereStr += " (";
                if (mChkboxNotDownload.Checked)
                {
                    whereStr += " hm_status = 0 or ";
                }
                if (mChkboxDownloaded.Checked)
                {
                    whereStr += " hm_status = 1 or ";
                }
                if (mChkboxSeen.Checked)
                {
                    whereStr += " hm_status = 2 or ";
                }
                if (mChkboxToDelete.Checked)
                {
                    whereStr += " hm_status = 3 or ";
                }
                if (mChkboxDeleted.Checked)
                {
                    whereStr += " hm_status = 4 or ";
                }

                whereStr += " 0) and ";

            }

            //3.released year
            string year = this.mTxtYear1.Text;
            if (!string.IsNullOrWhiteSpace(year) && Regex.IsMatch(year, "^\\d+$"))
            {
                whereStr += $" released_year >= '{year}' and ";
            }
            year = this.mTxtYear2.Text;
            if (!string.IsNullOrWhiteSpace(year) && Regex.IsMatch(year, "^\\d+$"))
            {
                whereStr += $" released_year <= '{year}' and ";
            }
            //4.douban score
            string doubanScore = mTxtDoubanScore1.Text.Trim();
            if (!string.IsNullOrWhiteSpace(doubanScore) && Regex.IsMatch(doubanScore, "^[0-9]+(.[0-9]{1,3})?$"))
            {
                whereStr += $" douban_score >= '{doubanScore}' and ";
            }
            doubanScore = mTxtDoubanScore2.Text.Trim();
            if (!string.IsNullOrWhiteSpace(doubanScore) && Regex.IsMatch(doubanScore, "^[0-9]+(.[0-9]{1,3})?$"))
            {
                whereStr += $" douban_score <= '{doubanScore}' and ";
            }
            //5.country type
            string district = mTxtDistrict.Text.Trim();
            if (!string.IsNullOrWhiteSpace(district))
            {
                if(district.Contains("/"))
                {
                    string[] strArr = district.Split('/');
                    for(int i = 0; i < strArr.Length; ++i)
                    {
                        whereStr += $" locate('{strArr[i]}', country_type) and ";
                    }
                }
                else
                    whereStr += $" locate('{district}', country_type) and ";
            }
            //6.movie type
            string movieType = mTxtType.Text.Trim();
            if (!string.IsNullOrWhiteSpace(mTxtType.Text.Trim()))
            {
                if(movieType.Contains("/"))
                {
                    string[] strArr = movieType.Split('/');
                    for(int i = 0; i< strArr.Length; ++i)
                    {
                        whereStr += $" locate('{strArr[i]}', movie_type) and ";
                    }
                }
                else
                    whereStr += $" locate('{mTxtType.Text.Trim()}', movie_type) and ";
            }
            //7.remark
            string remark = mMulTxtRemark.Text.Trim();
            if (!string.IsNullOrWhiteSpace(remark))
            {
                whereStr += $" locate('{remark}', remark) and ";
            }

            whereStr += "1";


            string sql = string.Format("select SQL_CALC_FOUND_ROWS id, cn_name as '中文名',en_name as '英文名', douban_score as '豆瓣'," +
                "released_year as '上映年代', " +
                "(case hm_status" +
                " when 0 then '未下载'" +
                " when 1 then '已下载'" +
                " when 2 then '已看'" +
                " when 3 then '要删'" +
                " when 4 then '已删' end) as '状态', movie_type as '类型', country_type as '拍摄地区', movie_duration as '片长(分钟)', remark as '注释', update_time as '更新时间' from " + dbSearchTableName);
            
            //note the $ usage
            sql += whereStr + $" limit {(curPageIndex - 1) * (ulong)CommonSql.RecCountPerPage},{CommonSql.RecCountPerPage};";

            try
            {
                string errStr = string.Empty;
                if (CommonSql.MysqlConn.State == ConnectionState.Closed)
                    //waiting  Common.AsyncOpenDB() to be finished, then run the following code
                    errStr = await CommonSql.AsyncOpenDB();

                if (errStr == string.Empty)
                {
                    //have to search together,then get 2 tables, one contains record data, the other contains totalCount
                    string sql2 = "select FOUND_ROWS() as totalCount;";
                    sql += sql2;
                    MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sql, CommonSql.ConnectionString);
                    DataSet dataSet = new DataSet();
                    //2 tables into dataset
                    mySqlDataAdapter.Fill(dataSet);
                    dataSet.Tables[0].TableName = "hm_video";
                    dataSet.Tables[1].TableName = "hm_totalCount";
                    //
                    //string sql2 = "select FOUND_ROWS() as totalCount;";
                    //MySqlCommand comm = new MySqlCommand(sql2, CommonSql.MysqlConn);
                    //recTotalCount = Convert.ToInt64(comm.ExecuteScalar());
                    
                    recTotalCount = Convert.ToUInt64(dataSet.Tables["hm_totalCount"].Rows[0].ItemArray[0]);
                    totalPageCount = Convert.ToUInt64(Math.Ceiling(recTotalCount / (float)CommonSql.RecCountPerPage));

                    curPageRecCount = dataSet.Tables[0].Rows.Count;
                    mLblRecCntInfo.Text = "本页" + curPageRecCount + "/" + recTotalCount + "条";
                    //set page data
                    mLblTotalPageCount.Text = "/" + totalPageCount + "页";
                    mTxtCurPageIndex.Text = curPageIndex.ToString();
                    //first remove here, or the button column's index will be changed to 0, not the last column,
                    //and add at the following place
                    if (this.dtGridView.Columns.Count > 0 && this.dtGridView.Columns[this.dtGridView.Columns.Count - 1].Name == "edit")
                        this.dtGridView.Columns.Remove("edit");
                    //
                    dtGridView.DataSource = dataSet.Tables["hm_video"];
                    //if (this.dtGridView.Columns.Count == 11)
                    //    this.dtGridView.Columns["edit"].DisplayIndex = this.dtGridView.Columns.Count - 1;
                        //10 columns
                    this.dtGridView.Columns[0].Name = "id";
                    this.dtGridView.Columns[1].Name = "cn_name";
                    this.dtGridView.Columns[2].Name = "en_name";
                    this.dtGridView.Columns[3].Name = "douban_score";
                    this.dtGridView.Columns[4].Name = "released_year";
                    this.dtGridView.Columns[5].Name = "hm_status";
                    this.dtGridView.Columns[6].Name = "movie_type";
                    this.dtGridView.Columns[7].Name = "country_type";
                    this.dtGridView.Columns[8].Name = "movie_duration";
                    this.dtGridView.Columns[9].Name = "remark";
                    this.dtGridView.Columns[10].Name = "update_time";
                    //add button column here
                    AddButtonColumn();
                    //
                    this.dtGridView.Columns["id"].ReadOnly = true;
                    this.dtGridView.Columns["update_time"].ReadOnly = true;

                    snackBar.BarType = MaterialSnackBar.SnackBarType.Suceesss;
                    snackBar.Text = "查询成功";
                }
                else
                {
                    snackBar.BarType = MaterialSnackBar.SnackBarType.Danger;
                    snackBar.Text = errStr;
                }

            }
            catch (MySqlException ex)
            {
                snackBar.BarType = MaterialSnackBar.SnackBarType.Danger;
                snackBar.Text = ex.Message;
            }
            //
            if(snackBar.BarType != MaterialSnackBar.SnackBarType.Suceesss)
            {
                snackBar.Show(this);
            }
            
        }

        private void AddButtonColumn()
        {
            //
            DataGridViewButtonColumn btnColumn = new DataGridViewButtonColumn();
            btnColumn.Name = "edit";
            btnColumn.HeaderText = "操作";
            btnColumn.Text = "编辑";
            btnColumn.UseColumnTextForButtonValue = true;
            btnColumn.AutoSizeMode =
            DataGridViewAutoSizeColumnMode.AllCells;
            //btnColumn.DisplayIndex = displayIndex;
            this.dtGridView.Columns.Add(btnColumn);
        }



        private void dtGridView_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            MaterialSnackBar snackBar = new MaterialSnackBar("", MaterialSnackBar.SnackBarType.Danger);
            try
            {
                //click edit button column in DataGridView
                //using the column name to compare just much safe
                if (this.dtGridView.Columns[e.ColumnIndex].Name != "edit")
                    return;
                //
                int videoCat = 0;
                if (this.mRadioMovie.Checked)
                    videoCat = 0;
                else if (this.mRadioTVSeries.Checked)
                    videoCat = 1;
                string id = this.dtGridView.Rows[e.RowIndex].Cells[0].Value.ToString();
                string cnName = this.dtGridView.Rows[e.RowIndex].Cells[1].Value.ToString();
                string enName = this.dtGridView.Rows[e.RowIndex].Cells[2].Value.ToString();
                string doubanScore = this.dtGridView.Rows[e.RowIndex].Cells[3].Value.ToString();
                string year = this.dtGridView.Rows[e.RowIndex].Cells[4].Value.ToString();
                string hmStatusStr = this.dtGridView.Rows[e.RowIndex].Cells[5].Value.ToString();
                string type = this.dtGridView.Rows[e.RowIndex].Cells[6].Value.ToString();
                string district = this.dtGridView.Rows[e.RowIndex].Cells[7].Value.ToString();
                string duration = this.dtGridView.Rows[e.RowIndex].Cells[8].Value.ToString();
                string remark = this.dtGridView.Rows[e.RowIndex].Cells[9].Value.ToString();
                //

                AddEditForm addEditForm = new AddEditForm(videoCat, id, cnName, enName, doubanScore,
                 year, hmStatusStr, type, district, duration, remark);
                addEditForm.ShowDialog(this);
            }
            catch (Exception ex)
            {
                snackBar.Text = ex.Message;
                snackBar.Show(this);
            }
        }
        /// <summary>
        /// change theme for UI
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnTheme_Click(object sender, EventArgs e)
        {
            if (Common.MSkinManager.Theme == MaterialSkinManager.Themes.LIGHT)
            {
                Common.MSkinManager.Theme = MaterialSkinManager.Themes.DARK;
            }
            else if (Common.MSkinManager.Theme == MaterialSkinManager.Themes.DARK)
            {
                Common.MSkinManager.Theme = MaterialSkinManager.Themes.LIGHT;
            }
            updateColor();

            Properties.Settings.Default.Theme = (int)Common.MSkinManager.Theme;
            Properties.Settings.Default.Save();

        }

        /// <summary>
        /// change color for UI
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnColor_Click(object sender, EventArgs e)
        {
            
            colorSchemeIndex++;
            if (colorSchemeIndex > 2)
                colorSchemeIndex = 0;

            updateColor();

            Properties.Settings.Default.ColorSchemeIndex = colorSchemeIndex;
            Properties.Settings.Default.Save();
        }

        private void setColor()
        {
            switch (colorSchemeIndex)
            {
                case 0:
                    Common.MSkinManager.ColorScheme = new ColorScheme(
                        Common.MSkinManager.Theme == MaterialSkinManager.Themes.DARK ? Primary.Teal500 : Primary.Indigo500,
                        Common.MSkinManager.Theme == MaterialSkinManager.Themes.DARK ? Primary.Teal700 : Primary.Indigo700,
                        Common.MSkinManager.Theme == MaterialSkinManager.Themes.DARK ? Primary.Teal200 : Primary.Indigo100,
                        Accent.Pink200,
                        TextShade.WHITE);
                    break;

                case 1:
                    Common.MSkinManager.ColorScheme = new ColorScheme(
                        Primary.Green600,
                        Primary.Green700,
                        Primary.Green200,
                        Accent.Red100,
                        TextShade.WHITE);
                    break;

                case 2:
                    Common.MSkinManager.ColorScheme = new ColorScheme(
                        Primary.BlueGrey800,
                        Primary.BlueGrey900,
                        Primary.BlueGrey500,
                        Accent.LightBlue200,
                        TextShade.WHITE);
                    break;
            }
        }

        private void updateColor()
        {
            //These are just example color schemes
            setColor();
            //
            SetActionPanelTheme();
            this.dtGridView.DefaultCellStyle.ForeColor = SystemColors.ControlText;
            this.dtGridView.DefaultCellStyle.BackColor = SystemColors.Window;
            //
            Invalidate();
        }

        private void btnDonateAbout_Click(object sender, EventArgs e)
        {
            DonateAboutForm donateAboutForm = new DonateAboutForm();
            donateAboutForm.ShowDialog(this);
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            //release db connection
            CommonSql.AsyncCloseDB();

            Environment.Exit(0);

        }


        const int WM_KEYDOWN = 256;
        const int WM_SYSKEYDOWN = 260;
        //overriding WndProc(ref Message m) to accept the key message seems failed
        protected override bool ProcessCmdKey(ref System.Windows.Forms.Message msg, System.Windows.Forms.Keys keyData)
        {

            if (msg.Msg == WM_KEYDOWN | msg.Msg == WM_SYSKEYDOWN)
            {
                switch (keyData)
                {
                    case Keys.Enter:
                        this.mBtnSearch_Click(null, null);
                        return true;
                        break;
                    case Keys.Home:
                        this.mBtnFirstPage_Click(null, null);
                        return true;
                        break;
                    case Keys.End:
                        this.mBtnLastPage_Click(null, null);
                        return true;
                        break;
                    case Keys.PageUp:
                        this.mBtnPrePage_Click(null, null);
                        return true;
                        break;
                    case Keys.PageDown:
                        this.mBtnNextPage_Click(null, null);
                        return true;
                        break;

                }

            }

            return false;
        }

    }
}
